﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO

Partial Class RelPISCOFINSImportacao
    Inherits System.Web.UI.Page

   
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        Try
            'If Not Session("Logged") Then
            '    Session("ShowSessionExpired") = 1 ' Show lblSessionExpired in Login.aspx
            '    Response.Redirect("Login.aspx")
            'End If

            If Not Page.IsPostBack Then

                Dim strConnection As String
                strConnection = "A"

                ' Header
                lblModulo.Text = "Administrador"
                lblModuloItem.Text = "PIS e COFINS - Importação"

                btnImprimir.Enabled = False


                If (LoadHolding(strConnection) = True) Then

                    FillDDL()

                    btnImprimir.Enabled = True

                    'If (CarregaGVW() = True) Then
                    '    btnImprimir.Enabled = True
                    'End If

                End If

            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function LoadHolding(ByVal strConn As String) As Boolean

        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT CodigoDaHolding, NomeDaHolding AS Holding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then

                    Do While theSqlDataReader.Read()
                        lblHolding.Text = theSqlDataReader(1).ToString
                        lblHolding.ToolTip = theSqlDataReader(0).ToString
                    Loop

                    LoadHolding = True

                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, CodigoDaCia + ' - ' + NomeDaCia AS Cia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "Cia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                If Not (ddlCompanhia.Items.Count = 1) Then
                    btnPesquisar.Enabled = True
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub FillDDLEstabelecimento()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDoEstabelecimento, CodigoDoEstabelecimento + ' - ' + RazaoSocialDoEstabelecimento AS Estabelecimento" & _
                     " FROM Estabelecimento" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlEstabelecimento.DataTextField = "Estabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub ddlCompanhia_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCompanhia.SelectedIndexChanged

        FillDDLEstabelecimento()

    End Sub

    Private Function DataValid() As Boolean

        DataValid = False

        If (ddlCompanhia.Text = "") Then
            lblMsg.Text = "Companhia deve ser selecionada."
            Exit Function
        End If

        If (ddlEstabelecimento.Text = "") Then
            lblMsg.Text = "Estabelecimento deve ser selecionado."
            Exit Function
        End If

        If (Trim(txtDataDe.Text) = "") Then
            lblMsg.Text = "Data (de) deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataDe.Text) = True Then
            lblMsg.Text = "Data (de) inválida."
            Exit Function
        End If

        If (Trim(txtDataAte.Text) = "") Then
            lblMsg.Text = "Data (até) deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataAte.Text) = True Then
            lblMsg.Text = "Data (até) inválida."
            Exit Function
        End If

        If (CDate(txtDataDe.Text) > CDate(txtDataAte.Text)) Then
            lblMsg.Text = "Data (de) deve ser menor ou igual que a Data (até)."
            Exit Function
        End If

        DataValid = True

    End Function

    Protected Sub btnImprimir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImprimir.Click

        If (rbPIS_COFINS.SelectedItem.Value = "PisCofins") Then
            Imprime_PIS_COFINS()
            Exit Sub
        ElseIf (rbPIS_COFINS.SelectedItem.Value = "PisCofinsFornecedor") Then
            Imprime_PIS_COFINS_Fornecedor()
            Exit Sub
        End If

        If Not (DataValid() = True) Then
            Exit Sub
        End If

        Dim sDataInicio, sDataFim As String

        sDataInicio = "'" & FormatMediumDate(txtDataDe.Text) & "'"
        sDataFim = "'" & FormatMediumDate(txtDataAte.Text) & "'"

        Dim mydatatable As New DataTable
        Dim theQueryString As String
        theQueryString = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

 
            If (ddlEstabelecimento.SelectedItem.Text <> "" And Trim(txtDataDe.Text) <> "" And txtDataAte.Text <> "") Then

                theQueryString = "SELECT A.CodigoDaCia, A.CodigoDoEstabelecimento, B.DataChegadaFabrica, A.DI, B.CodFornecedor, C.RazaoSocialFornecedor, A.CodItem, " & _
                        "D.DscItem, A.CodClassificacaoFiscal, A.ValorBaseCalculoPIS, A.ValorPIS, A.ValorBaseCalculoCOFINS, A.ValorCOFINS " & _
                        "FROM didetail A, diheader B, fornecedor C, item D " & _
                        "WHERE A.CodigoDaCia = B.CodigoDaCia AND " & _
                        "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento AND " & _
                        "A.DI = B.DI AND  " & _
                        "B.CodFornecedor = C.CodFornecedor AND " & _
                        "A.CodigoDaCia = D.CodigoDaCia AND " & _
                        "A.CodigoDoEstabelecimento = D.CodigoDoEstabelecimento AND " & _
                        "A.CodItem = D.CodItem AND " & _
                        "B.DataChegadaFabrica BETWEEN " & sDataInicio & " AND " & sDataFim & " AND " & _
                        "A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' AND " & _
                        "A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' " & _
                        "ORDER BY B.DataChegadaFabrica, A.DI, A.CodItem"

            End If

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                Dim grid As DataGrid = New DataGrid()
                grid.HeaderStyle.Font.Bold = True
                grid.DataSource = mydatatable
                grid.DataBind()

                Using sw As New StreamWriter(Me.Server.MapPath("PIS_COFINS_Importacao.xls"))
                    Using hw As New HtmlTextWriter(sw)
                        grid.RenderControl(hw)
                    End Using
                End Using

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Dim strServerPath As String
        Dim objSourceFileInfo As System.IO.FileInfo
        Dim strSuggestedName As String

        strSuggestedName = "PIS_COFINS_Importacao.xls"
        ' convert relative path to path on server machine
        strServerPath = Me.Server.MapPath(strSuggestedName)
        ' get fileinfo of source file
        objSourceFileInfo = New System.IO.FileInfo(strServerPath)
        ' if the file exists
        If objSourceFileInfo.Exists Then
            With Me.Response
                ' tell the browser what content type to expect
                .ContentType = "application/octet-stream"
                ' tell the browser to save rather than display inline
                .AddHeader("Content-Disposition", "attachment; filename=" & strSuggestedName)

                ' tell the browser how big the file is
                .AddHeader("Content-Length", objSourceFileInfo.Length.ToString)

                ' send the file to the browser
                .WriteFile(objSourceFileInfo.FullName)

                ' make sure response is sent
                .Flush()
                ' end response
                .End()
            End With
            ' if the file does not exist
        Else
            ' show error page
            lblMsg.Text = "Application Error"
        End If

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

    Private Sub Imprime_PIS_COFINS()

        Dim NewScript As String
        Dim tot As Double = 0
        Dim sDataInicio, sDataFim As String

        If Not (DataValid() = True) Then
            Exit Sub
        End If

 
        sDataInicio = "'" & FormatMediumDate(txtDataDe.Text) & "'"
        sDataFim = "'" & FormatMediumDate(txtDataAte.Text) & "'"
        'Dim ItemList As New ArrayList()

        'Deleta arquivo SOPA.xls da pasta do servidor
        IO.File.Delete(Server.MapPath("PIS_COFINS.xls"))

        NewScript = "<br>"
        NewScript = NewScript & ""
        NewScript = NewScript & "<br>"
        NewScript = NewScript & "<table border='1' align='Center' fontcolor='white'>"

        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cia</b></td>"
        NewScript = NewScript & "</script>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Estabelecimento</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>NFTerceiros</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Dt.Cheg.Fabrica</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cod.Item</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cl.Fiscal</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Vl.Base Calc.PIS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>%PIS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Valor PIS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Vl.Base Calc.COFINS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>%COFINS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Valor COFINS</b></td>"


        NewScript = NewScript & "</tr> "
        'Dim vCodigoDoFornecedor As String
        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT A.CodigoDaCia, A.CodigoDoEstabelecimento, C.NFTerceiros, B.DataChegadaFabrica, A.CodItem, " & _
                    "A.CodClassificacaoFiscal, A.ValorBaseCalculoPIS, A.PercentualPIS, A.ValorPIS, A.ValorBaseCalculoCOFINS, A.PercentualCOFINS, A.ValorCOFINS " & _
                    "FROM didetail A, diheader B, NFTerceirosHeader C " & _
                    "WHERE A.CodigoDaCia = B.CodigoDaCia AND " & _
                    "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento AND " & _
                    "A.DI = B.DI AND " & _
                    "A.CodigoDaCia = C.CodigoDaCia AND " & _
                    "A.CodigoDoEstabelecimento = C.CodigoDoEstabelecimento AND " & _
                    "A.DI = C.DI AND " & _
                    "B.DataChegadaFabrica BETWEEN " & sDataInicio & " AND " & sDataFim & " AND " & _
                    "A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' AND " & _
                    "A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' " & _
                    "ORDER BY C.NFTerceiros"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                tot = 0
                Do While theSqlDataReader.Read()
                    If Not (IsDBNull(Trim(theSqlDataReader(0)))) Then

                        NewScript = NewScript & "<tr>"
                        NewScript = NewScript & "<td style='width:60px'>"
                        NewScript = NewScript & theSqlDataReader!CodigoDaCia
                        NewScript = NewScript & "</td>"
                        NewScript = NewScript & "<td style='width:120px'>"
                        NewScript = NewScript & " " & Str(theSqlDataReader!CodigoDoEstabelecimento)
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!NFTerceiros
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!DataChegadaFabrica
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!CodItem
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!CodClassificacaoFiscal
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorBaseCalculoPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!PercentualPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorBaseCalculoCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!PercentualCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "</tr>"

                    End If
                    tot = tot + 1
                    If (tot = 1000) Then
                        Exit Do
                    End If
                Loop
                theSqlDataReader.Close()
                theSqlConnection.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        NewScript = NewScript & "</table>"


        Response.Clear()

        'Response.ContentType = "application/vnd.ms-excel"
        Response.ContentType = "application/msexcel"
        Response.AddHeader("content-disposition", "attachment;filename=PIS_COFINS.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        NewScript = NewScript & "<br><br>"
        'NewScript = NewScript & "<p align='Center'> Nota = Este e um documento dinamico do excel</p>"
        HttpContext.Current.Response.Write(NewScript)

        Try
            Response.ContentType = "application/vnd.ms-excel"
            'Response.ContentType = "application/msexcel"
            Response.AddHeader("content-disposition", "attachment;filename=PIS_COFINS.xls")
            Response.Charset = ""
            Dim textWriter As New IO.StringWriter()
            Dim htmlWriter As New System.Web.UI.HtmlTextWriter(textWriter)
            Response.Write(textWriter)
            Response.End()
        Catch ex As Exception
            lblMsg.Text = "Msg DB: " & ex.Message
        End Try

    End Sub

    Private Sub Imprime_PIS_COFINS_Fornecedor()

        Dim NewScript As String
        Dim tot As Double = 0
        Dim sDataInicio, sDataFim As String

        Dim strCodFornecedor As String
        Dim strValorBaseCalculoPIS As Double
        Dim strValorPIS As Double
        Dim strValorBaseCalculoCOFINS As Double
        Dim strValorCOFINS As Double

        strCodFornecedor = ""

        strValorBaseCalculoPIS = 0
        strValorPIS = 0
        strValorBaseCalculoCOFINS = 0
        strValorCOFINS = 0


        If Not (DataValid() = True) Then
            Exit Sub
        End If


        sDataInicio = "'" & FormatMediumDate(txtDataDe.Text) & "'"
        sDataFim = "'" & FormatMediumDate(txtDataAte.Text) & "'"
        'Dim ItemList As New ArrayList()

        'Deleta arquivo SOPA.xls da pasta do servidor
        IO.File.Delete(Server.MapPath("PIS_COFINS_Fornecedor.xls"))

        NewScript = "<br>"
        NewScript = NewScript & ""
        NewScript = NewScript & "<br>"
        NewScript = NewScript & "<table border='1' align='Center' fontcolor='white'>"

        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Dt.Cheg.Fabrica</b></td>"
        NewScript = NewScript & "</script>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>NF.Terceiros</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Serie</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cia</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Estabelecimento</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cod.Fornecedor</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Fornecedor</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cod.Item</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Item</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Fatura</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>DI</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Dt.Registro</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Desp.</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>N.Desp.</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Cl.Fiscal</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Vl.Base Calc.PIS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>%PIS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Valor PIS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Vl.Base Calc.COFINS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>%COFINS</b></td>"
        NewScript = NewScript & "<td bgcolor='#348017' style='color:white'><b>Valor COFINS</b></td>"


        NewScript = NewScript & "</tr> "
        'Dim vCodigoDoFornecedor As String
        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT B.DataChegadaFabrica, E.NFTerceiros, E.SerieNFTerceiros, A.CodigoDaHolding, A.CodigoDaCia, A.CodigoDoEstabelecimento, B.CodFornecedor, C.RazaoSocialFornecedor, A.CodItem,  " & _
                        "D.DscItem, A.Fatura, A.DI, B.DataRegistroDI, F.CodFornecedorServico, F.NumeroDocumento, A.CodClassificacaoFiscal, A.ValorBaseCalculoPIS, A.PercentualPIS, A.ValorPIS, A.ValorBaseCalculoCOFINS, A.PercentualCOFINS, A.ValorCOFINS " & _
                        "FROM didetail A, diheader B, fornecedor C, item D, NFTerceirosHeader E, controledespesasheader F " & _
                        "WHERE A.CodigoDaCia = B.CodigoDaCia AND " & _
                        "A.CodigoDoEstabelecimento = B.CodigoDoEstabelecimento AND " & _
                        "A.DI = B.DI AND " & _
                        "B.CodFornecedor = C.CodFornecedor AND " & _
                        "A.CodigoDaCia = D.CodigoDaCia AND " & _
                        "A.CodigoDoEstabelecimento = D.CodigoDoEstabelecimento AND " & _
                        "A.CodItem = D.CodItem AND " & _
                        "A.CodigoDaCia = E.CodigoDaCia AND " & _
                        "A.CodigoDoEstabelecimento = E.CodigoDoEstabelecimento AND " & _
                        "A.DI = E.DI AND " & _
                        "A.CodigoDaCia = F.CodigoDaCia AND " & _
                        "A.CodigoDoEstabelecimento = F.CodigoDoEstabelecimento AND " & _
                        "A.DI = F.DI AND " & _
                        "B.DataChegadaFabrica BETWEEN " & sDataInicio & " AND " & sDataFim & " AND " & _
                        "A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' AND " & _
                        "A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' " & _
                        "ORDER BY CONVERT(int, B.CodFornecedor), E.NFTerceiros"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                tot = 0
                Do While theSqlDataReader.Read()
                    If Not (IsDBNull(Trim(theSqlDataReader(0)))) Then

                        If (strCodFornecedor <> theSqlDataReader!CodFornecedor And tot > 0) Then
                            NewScript = NewScript & "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>"

                            NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                            NewScript = NewScript & strValorBaseCalculoPIS
                            NewScript = NewScript & "</td>"

                            NewScript = NewScript & "<td></td>"
                            NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                            NewScript = NewScript & strValorPIS
                            NewScript = NewScript & "</td>"

                            'NewScript = NewScript & "<td></td>"

                            'NewScript = NewScript & "<tr>"
                            NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                            NewScript = NewScript & strValorBaseCalculoCOFINS
                            NewScript = NewScript & "</td>"

                            NewScript = NewScript & "<td></td>"
                            NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                            NewScript = NewScript & strValorCOFINS
                            NewScript = NewScript & "</td>"

                            strCodFornecedor = theSqlDataReader!CodFornecedor
                            strValorBaseCalculoPIS = 0
                            strValorPIS = 0
                            strValorBaseCalculoCOFINS = 0
                            strValorCOFINS = 0

                            NewScript = NewScript & "<tr>"
                            NewScript = NewScript & "<tr>"

                        End If

                        NewScript = NewScript & "<tr>"
                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!DataChegadaFabrica
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!NFTerceiros
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!SerieNFTerceiros
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:60px'>"
                        NewScript = NewScript & theSqlDataReader!CodigoDaCia
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:120px'>"
                        NewScript = NewScript & " " & Str(theSqlDataReader!CodigoDoEstabelecimento)
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:120px'>"
                        NewScript = NewScript & theSqlDataReader!CodFornecedor
                        strCodFornecedor = theSqlDataReader!CodFornecedor
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:120px'>"
                        NewScript = NewScript & theSqlDataReader!RazaoSocialFornecedor
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!CodItem
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!DscItem
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!Fatura
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!DI
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!DataRegistroDI
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!CodFornecedorServico
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!NumeroDocumento
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!CodClassificacaoFiscal
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorBaseCalculoPIS
                        strValorBaseCalculoPIS = strValorBaseCalculoPIS + theSqlDataReader!ValorBaseCalculoPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!PercentualPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorPIS
                        strValorPIS = strValorPIS + theSqlDataReader!ValorPIS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorBaseCalculoCOFINS
                        strValorBaseCalculoCOFINS = strValorBaseCalculoCOFINS + theSqlDataReader!ValorBaseCalculoCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!PercentualCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "<td style='width:110px'>"
                        NewScript = NewScript & theSqlDataReader!ValorCOFINS
                        strValorCOFINS = strValorCOFINS + theSqlDataReader!ValorCOFINS
                        NewScript = NewScript & "</td>"

                        NewScript = NewScript & "</tr>"

                    End If
                    tot = tot + 1
                    If (tot = 1000) Then
                        Exit Do
                    End If
                Loop

                NewScript = NewScript & "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>"

                NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                NewScript = NewScript & strValorBaseCalculoPIS
                NewScript = NewScript & "</td>"

                NewScript = NewScript & "<td></td>"
                NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                NewScript = NewScript & strValorPIS
                NewScript = NewScript & "</td>"

                'NewScript = NewScript & "<td></td>"

                'NewScript = NewScript & "<tr>"
                NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                NewScript = NewScript & strValorBaseCalculoCOFINS
                NewScript = NewScript & "</td>"

                NewScript = NewScript & "<td></td>"
                NewScript = NewScript & "<td bgcolor='#348017' style='color:white' 'width:110px'>"
                NewScript = NewScript & strValorCOFINS
                NewScript = NewScript & "</td>"


                theSqlDataReader.Close()
                theSqlConnection.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        NewScript = NewScript & "</table>"


        Response.Clear()

        'Response.ContentType = "application/vnd.ms-excel"
        Response.ContentType = "application/msexcel"
        Response.AddHeader("content-disposition", "attachment;filename=PIS_COFINS_Fornecedor.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        NewScript = NewScript & "<br><br>"
        'NewScript = NewScript & "<p align='Center'> Nota = Este e um documento dinamico do excel</p>"
        HttpContext.Current.Response.Write(NewScript)

        Try
            Response.ContentType = "application/vnd.ms-excel"
            'Response.ContentType = "application/msexcel"
            Response.AddHeader("content-disposition", "attachment;filename=PIS_COFINS_Fornecedor.xls")
            Response.Charset = ""
            Dim textWriter As New IO.StringWriter()
            Dim htmlWriter As New System.Web.UI.HtmlTextWriter(textWriter)
            Response.Write(textWriter)
            Response.End()
        Catch ex As Exception
            lblMsg.Text = "Msg DB: " & ex.Message
        End Try

    End Sub

End Class
